#!/bin/bash

APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi 


dim_user_zip="
insert overwrite table $APP.dim_user_zip partition (dt)
select
           id,
            login_name,
            nick_name,
            name,
            phone_num,
            email,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            start_date,
            if(rn=2,date_sub('$do_date',1),end_date) end_date,
            if(rn=1,'9999-12-31',date_sub('$do_date',1)) dt
    from
    (select id,
            login_name,
            nick_name,
            name,
            phone_num,
            email,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            start_date,
            end_date,
            row_number() over (partition by id order by start_date desc) rn
     from (select id,
                  login_name,
                  nick_name,
                  name,
                  phone_num,
                  email,
                  user_level,
                  birthday,
                  gender,
                  create_time,
                  operate_time,
                  start_date,
                  end_date
           from $APP.dim_user_zip
           where dt = "9999-12-31"
           union
           select id,
                  login_name,
                  nick_name,
                  name,
                  phone_num,
                  email,
                  user_level,
                  birthday,
                  gender,
                  create_time,
                  operate_time,
                  start_date,
                  end_date
           from (
                    select data.id,
                           data.login_name,
                           data.nick_name,
                           md5(data.real_name)                                         name,
                           md5(if(data.phone_num regexp
                                  '^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\d{8}$',
                                  data.phone_num, null))                               phone_num,
                           md5(if(data.email regexp '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\\.[a-zA-Z0-9_-]+)+$', data.email,
                                  null))                                               email,
                           data.user_level,
                           data.birthday,
                           data.gender,
                           data.create_time,
                           data.operate_time,
                           '$do_date'                                                start_date,
                           '9999-12-31'                                                end_date,
                           row_number() over (partition by data.id order by ts desc) rn
                    from $APP.ods_user_info_inc eo
                    where eo.dt = "$do_date"
                ) t1
           where rn = 1
          )t2
    )t3;
    "

dim_province_full="
    insert overwrite table $APP.dim_province_full partition(dt='$do_date')
    select
        id,
        name,
        area_code,
        iso_code,
        iso_3166_2,
        region_id
    from $APP.ods_base_province_full
    where dt='$do_date';
   "
dim_course_full="
    with course as (
        select id,
            course_name,
            course_slogan,
            course_cover_url,
            subject_id,
            teacher,
            publisher_id,
            chapter_num,
            origin_price,
            reduce_amount,
            actual_price,
            course_introduce,
            create_time,
            update_time,
            deleted
        from $APP.ods_course_info_full
        where dt = '$do_date'
        ),
        subject as (
            select id,
                    subject_name,
                    category_id
            from $APP.ods_base_subject_info_full
            where dt = '$do_date'
        ),
        category as(
            select id,
                    category_name
            from $APP.ods_base_category_info_full
            where dt = '$do_date'
        )
    insert overwrite table $APP.dim_course_full partition(dt='$do_date')
    select
        course.id,
        origin_price,
        course_name,
        course_introduce,
        chapter_num,
        deleted,
        subject_id,
        subject_name,
        category_id ,
        category_name,
        teacher,
        publisher_id ,
        course_slogan,
        course_cover_url,
        actual_price,
        reduce_amount,
        create_time,
        update_time
    from course
    left join subject
        on course.subject_id = subject.id
    left join category
        on category.id = subject.category_id;
    "

    dim_video_full="
    insert overwrite table $APP.dim_video_full partition (dt = '$do_date')
    select v.id,
        v.video_name,
        v.during_sec,
        v.video_status,
        v.video_size,
        v.video_url,
        v.video_source_id,
        v.version_id,
        v.course_id,
        ci.course_name,
        v.publisher_id,
        v.create_time,
        v.update_time,
        v.deleted,
        v.chapter_id,
        c.chapter_name,
        c.is_free
    from $APP.ods_video_info_full v
        left join $APP.ods_chapter_info_full c
            on v.chapter_id = c.id
        left join (
            select id,
                course_name
                from
            $APP.ods_course_info_full
            where dt = '$do_date'
            ) ci
            on v.course_id = ci.id
    where v.dt = '$do_date';
    "
    dim_paper_full="
    with tparque as (
        select paper_id,
            question_id,
            score,
            create_time,
            deleted
        from $APP.ods_test_paper_question_full
        where dt = '$do_date'
    ),
        tpar as (
            select id,
                    paper_title
            from $APP.ods_test_paper_full
            where dt = '$do_date'
        ),
    tpointque as (
        select point_id,
            question_id
        from $APP.ods_test_point_question_full
        where dt = '$do_date'
    ),klpoint as(
        select id,
            point_txt,
            point_level
        from $APP.ods_knowledge_point_full
        where dt = '$do_date'
    ),tqueinfo as (
        select id,
            question_txt,
            chapter_id,
            course_id,
            question_type
        from $APP.ods_test_question_info_full
        where dt = '$do_date'
    ),tqueop as (
        select option_txt,
            question_id,
            is_correct,
            id option_id
        from $APP.ods_test_question_option_full
        where dt = '$do_date'
    ),course as (
        select id,
            course_name
        from $APP.ods_course_info_full
        where dt = '$do_date'
    ),chapter as (
        select id,
            chapter_name
        from $APP.ods_chapter_info_full
        where dt = '$do_date'
    )
    insert overwrite table $APP.dim_paper_full partition(dt='$do_date')
    select
    paper_id,
    paper_title,
    tparque.question_id,
    score,
    create_time,
    deleted,
    point_id,
    point_txt ,
    point_level,
    question_txt,
    question_type,
    chapter_id,
    chapter_name,
    course_id,
    course_name,
    option_txt,
    is_correct,
    option_id
    from tpar
    left join tparque
        on tpar.id = tparque.paper_id
    left join tpointque
        on tparque.question_id = tpointque.question_id
    left join klpoint
        on klpoint.id = tpointque.point_id
    left join tqueinfo
        on tqueinfo.id = tparque.question_id
    left join tqueop
        on tqueop.question_id = tparque.question_id
    left join course
        on course.id = tqueinfo.course_id
    left join chapter
        on chapter.id = tqueinfo.chapter_id;
    "
dim_vip_full="
    insert overwrite table $APP.dim_vip_full partition(dt='$do_date')
    select
        vip.data.id,
        vip.data.user_id,
        us.data.nick_name,
        vip.data.from_vip,
        vip.data.to_vip,
        vip.data.create_time
    from $APP.ods_vip_change_detail_inc vip
    left join $APP.ods_user_info_inc us
    on  vip.data.user_id = us.data.id
    where vip.dt = '$do_date'
    and us.dt = '$do_date'
    and vip.type='insert'
    and us.type = 'insert';
   "
case $1 in
"dim_user_zip")
    hive -e "$dim_user_zip"
;;
"dim_province_full")
    hive -e "$dim_province_full"
;;
"dim_video_full")
    hive -e "$dim_video_full"
;;
"dim_course_full")
    hive -e "$dim_course_full"
;;
"dim_paper_full")
    hive -e "$dim_paper_full"
;;
"dim_vip_full")
    hive -e "$dim_vip_full"
;;
"all")
    hive -e "$dim_user_zip$dim_province_full$dim_video_full$dim_course_full$dim_paper_full$dim_vip_full"
;;
esac
